<?php

declare(strict_types=1);

namespace App\Utils;

use Exception;
use Hyperf\Di\Annotation\Inject;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Hyperf\HttpMessage\Stream\SwooleStream;
use Hyperf\HttpServer\Contract\ResponseInterface;
use Hyperf\Utils\ApplicationContext;


/**
 * excel导出函数
 *
 * @Author czm
 * @DateTime 2020-05-19
 */
class Excel
{

    private $columnIndex;
    //文件位置
    private $fileDir = "/runtime/static/excel";
    private $filePath = "";

    public function __construct()
    {
        $this->container = ApplicationContext::getContainer();
        $this->response = $this->container->get(ResponseInterface::class);
        $this->deleteExpiredFile();
    }
    //少量数据使用案例
    //导出文件名称
    // $filename='导出文件名称.xlsx';
    //表头映射与字段排序
    // $head=['id'=>'id','name'=>'122132'];
    //导出内容二维数组
    //$data=[
    // ['id'=>1,'name'=>'测试']
    //];
    //return \App\Utils\Excel::create($head,$data)->download($filename);

    // 少于十万条数据可调用task进程下载导出
    // $content=$this->container->get(\App\TaskService\ExcelTask::class)->raw($head,$data);
    // return \App\Utils\Excel::download($filename,$content);

    //  百万数量导出异步url
    //  $filename="导出文件名称.xlsx";
    //  $unquieName=Excel::genZipName();
    //  $driver=$this->container->get(\Hyperf\AsyncQueue\Driver\DriverFactory::class)->get('default');
    //  $driver->push(new \App\Job\ExcelJob(['unquieName'=>$unquieName,'scene'=>'order']));
    //  return Excel::downloadLink($filename,$unquieName);

    /**
     * @Author czm
     * @DateTime 2020-05-20
     * @param array $head 表头映射与字段排序 $head=['id'=>'id','name'=>'122132'];
     * @param array $data 导出内容二维数组   $data=[ ['id'=>1,'name'=>'测试']];
     * @return void
     */
    protected function create(array $head, array $data)
    {
        $this->spreadsheet = new Spreadsheet();
        $this->columnIndex = $this->stringFromColumnIndex(count($head));
        $sheet = $this->spreadsheet->getActiveSheet();
        $headKey = array_keys($head);
        $headValue = array_values($head);
        //设置头部
        $i = 1;
        foreach ($headValue as $value) {
            $sheet->setCellValueByColumnAndRow($i++, 1, " " . $value . " ");
            $sheet->getColumnDimension(current($this->columnIndex))->setAutoSize(true);
            next($this->columnIndex);
        }
        sort($this->columnIndex);
        //设置内容
        $j = 1;
        foreach ($data as $value) {
            $i = 1;
            $j++;
            foreach ($headKey as $val) {
                $sheet->setCellValueByColumnAndRow($i++, $j, $value[$val] ?? '');
            }
        }
        $fontHead = current($this->columnIndex) . '1:' . end($this->columnIndex) . '1';
        $sheet->getStyle($fontHead)->getFont()->setBold(true)->setSize(14);
        //居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        reset($this->columnIndex) && $center = current($this->columnIndex) . '1:' . end($this->columnIndex) . $j;
        reset($this->columnIndex) && $sheet->getStyle($center)->applyFromArray($styleArray);
        unset($sheet);
        return $this;
    }

    //文件追加内容
    protected function append(array $head, array $data, string $file, int $offset = 0)
    {
        if (!file_exists($file)) {
            throw new Exception("追加文件不存在！");
        }
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(false);
        $this->spreadsheet = $reader->load($file); //载入excel表格
        $sheet = $this->spreadsheet->getActiveSheet();
        if (empty($offset)) {
            $offset = $sheet->getHighestRow() + 1; // 总行数
        }
        $j = $offset;
        $this->columnIndex = $this->stringFromColumnIndex(count($head));
        $headKey = array_keys($head);
        //追加内容
        foreach ($data as $value) {
            $i = 1;
            foreach ($headKey as $val) {
                $sheet->setCellValueByColumnAndRow($i++, $j, $value[$val] ?? '');
            }
            $j++;
        }

        //居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $center = current($this->columnIndex) . $offset . ':' . end($this->columnIndex) . $j;
        reset($this->columnIndex) && $sheet->getStyle($center)->applyFromArray($styleArray);
        return $this;
    }

    /**
     * 保存生成excel文件并返回路径信息
     * @Author czm
     * @DateTime 2020-05-19
     * @param [type] $filename
     * @return void
     */
    protected function save()
    {
        $filename = $this->genXlsxName();
        $fileDir = BASE_PATH . $this->fileDir;
        !is_dir($fileDir) && mkdir($fileDir, 0777, true);
        $writer = new Xlsx($this->spreadsheet);
        $this->filePath = $fileDir . "/" . $filename;
        $writer->save($this->filePath);
        unset($writer);
        $this->reset();
        return $this->fileDir . "/" . $filename;
    }

    /**
     * 直接输出excel文件repsonser对象
     * @DateTime 2020-05-19
     * @param [type] $filename 文件名称 导出文件名称.xlsx
     * @return void
     */
    protected function download($filename, $content = "")
    {
        if (empty($content)) {
            $content = $this->raw();
        }

        return $this->response
            ->withHeader('content-description', 'File Transfer')
            ->withHeader('content-type', "application/vnd.ms-excel")
            ->withHeader('content-disposition', "attachment; filename={$filename}")
            ->withHeader('content-transfer-encoding', 'binary')
            ->withHeader('pragma', 'no-cache')
            // ->withHeader('etag', $etag)
            // ->withBody(new SwooleStream($content));
            ->withContent($content);
    }

    /**
     * 返回SwooleStream封装的对象
     * @Author czm
     * @DateTime 2020-05-19
     * @return void
     */
    public function raw()
    {
        $writer = ApplicationContext::getContainer()->get(Xlsx::class, [$this->spreadsheet]);
        ob_start();
        $writer->save('php://output');
        $content = ob_get_clean();
        $this->reset();
        return $content;
    }

    /** 文档导出
     * @param bool $data 导出内容数组
     * @return str 文档下载链接url
     * @author abell
     * @since 2020-05-19
     */
    public function output($heads, $data)
    {
        $alpha_str = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; //处理列少于26的数据使用
        $spread_sheet = new Spreadsheet();
        $work_sheet = $spread_sheet->getActiveSheet();
        // 1 处理表头
        $work_sheet->setTitle($heads['titile']); //sheet名设置
        // 处理前2行数据
        $work_sheet->setCellValueByColumnAndRow(1, 1, $heads['titile']);
        $keys_array = $heads['keys_array'];
        $keys_list = array_keys($keys_array);
        $key_num = 1;
        foreach ($keys_array as $key_value) {
            $work_sheet->setCellValueByColumnAndRow($key_num, 2, $key_value);
            $key_num++;
        }
        // 表头合并单元格
        $head_length_alpha = substr($alpha_str, count($keys_list) - 1, 1);
        $work_sheet->mergeCells('A1:' . $head_length_alpha . '1');
        // 2 处理主要数据
        for ($i = 0; $i < count($data); $i++) {
            $j = $i + 3;
            $data_num = 1;
            foreach ($keys_list as $data_key) {
                $work_sheet->setCellValueByColumnAndRow($data_num, $j, $data[$i][$data_key]);
                $data_num++;
            }
        }
        // 3 保存excel文件
        $filePath = substr(config('web.img_path'), 0, 13) . 'excel/' . date("Ymd") . "/";
        !is_dir(BASE_PATH . $filePath) && @mkdir(BASE_PATH . $filePath, 0777, true);
        $filename = $filePath . uniqid() . '.xlsx';
        $writer = new Xlsx($spread_sheet);
        $writer->save(BASE_PATH . $filename);
        return  config('web.web_host') . substr($filename, 12);
    }

    /** 文档导入解析
     * @param file $file 导入的文件
     * @return array 文档数据解析处理
     * @author abell
     * @since 2020-05-19
     */
    public function excel($file)
    {
        $upload_file = make(Upload::class)->upload_file($file);
        $file_path = BASE_PATH . $upload_file;
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spread_sheet = $reader->load($file_path);
        $work_sheet = $spread_sheet->getActiveSheet();
        $highest_row = $work_sheet->getHighestRow();
        $highest_column = $work_sheet->getHighestColumn();
        $highest_column_index = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_column);
        // 处理标题
        $work_title = $work_sheet->getCellByColumnAndRow(1, 1)->getValue();
        // 处理表头
        $head_array = array();
        for ($i = 1; $i < $highest_column_index; $i++) {
            array_push($head_array, $work_sheet->getCellByColumnAndRow($i, 2)->getValue());
        }
        // 处理主要内容
        $content_array = array();
        for ($j = 3; $j <= $highest_row; $j++) {
            $box_array = array();
            for ($i = 1; $i < $highest_column_index; $i++) {
                array_push($box_array, $work_sheet->getCellByColumnAndRow($i, $j)->getValue());
            }
            array_push($content_array, $box_array);
        }
        return [
            'title' => $work_title, 'heads' => $head_array, 'contents' => $content_array
        ];
    }


    //生成excel列数
    protected function stringFromColumnIndex($pColumnIndex = 26)
    {
        $_indexCache = array();
        while ($pColumnIndex) {
            $pColumnIndex--;
            if (!isset($_indexCache[$pColumnIndex])) {
                // Determine column string
                if ($pColumnIndex < 26) {
                    $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
                } elseif ($pColumnIndex < 702) {
                    $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26);
                } else {
                    $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26);
                }
            }
        }
        sort($_indexCache);
        return $_indexCache;
    }

    protected function getUniqueName()
    {
        return uniqid((string) mt_rand(), false);
    }

    /**
     * 生成xlsx文件名称
     * @Author czm
     * @DateTime 2020-05-20
     * @return void
     */
    protected function genXlsxName()
    {
        return $this->getUniqueName() . '.xlsx';
    }

    /**
     * 生成zip文件名称
     * @Author czm
     * @DateTime 2020-05-20
     * @return void
     */
    protected function genZipName()
    {
        return $this->getUniqueName() . '.zip';
    }

    /**
     * 压缩文件
     * @Author czm
     * @DateTime 2020-05-20
     * @param [type] $filename 压缩生成的文件名
     * @param [type] $files    需要被压缩的文件列表
     * @return void
     */
    protected function zipFiles($filename, $files)
    {
        $zip = new \ZipArchive();
        $filename = BASE_PATH . $this->fileDir . '/' . $filename;
        $this->filePath = $filename;
        $zip->open($filename, \ZipArchive::CREATE);   //打开压缩包
        foreach ($files as $k => $file) {
            $zip->addFile(BASE_PATH . $file, ++$k . '.xlsx');   //向压缩包中添加文件

        }
        $zip->close();
        foreach ($files as $k => $file) {
            unlink(BASE_PATH . $file);
        }
        return  $this;
    }

    /**
     * 压缩文件并返回响应下载对象
     * @Author czm
     * @DateTime 2020-05-20
     * @param [type] $filename 压缩生成的文件名
     * @param [type] $files    需要被压缩的文件列表
     * @return void
     */
    protected function zipFilesDownload($filename, $files)
    {
        $unquieName = $this->genZipName();
        $this->zipFiles($unquieName, $files);
        return  $this->response->download($this->filePath, $filename . "." . pathinfo($unquieName)['extension']);;
    }

    /**
     * 返回文档的相对路径目录
     * @Author czm
     * @DateTime 2020-05-20
     * @return void
     */
    protected function getFileDir()
    {
        return $this->fileDir;
    }

    /**
     * 根据文件uniqueName生成下载链接
     * @Author czm
     * @DateTime 2020-05-21
     * @param [type] $filename  文件命名
     * @param [type] $uniqueName 需要下载的文件名 通过save或genZipName生成
     * @param string $route 下载url的路由信息不配置默认未$this->fileDir属性信息
     * @return void
     */
    protected function downloadLink($filename, $uniqueName, $route = "")
    {
        $path = $route ?: $this->fileDir;
        $uniqueName = str_replace($this->fileDir, "", $uniqueName);
        return $path . "?filename=" . $filename . "&uniqueName=" . $uniqueName;
    }


    protected function deleteExpiredFile()
    {
        $path = BASE_PATH . $this->getFileDir();
        if (!file_exists($path)) {
            return $this;
        }
        $data = scandir($path);
        foreach ($data as $value) {
            if ($value != '.' && $value != '..') {
                $file = $path . '/' . $value;
                $time = time() - 3600 * 24;
                ($time > fileatime($file)) && unlink($file);
            }
        }
        return $this;
    }

    protected function reset()
    {
        // unset($this->spreadsheet);
        return $this->spreadsheet->disconnectWorksheets();
    }

    protected function getFilePath()
    {
        return $this->filePath;
    }

    public function __call($method, $parameters)
    {
        return $this->{$method}(...$parameters);
    }

    public static function __callStatic($method, $arguments)
    {
        $instance = new static();
        if (method_exists($instance, $method)) {
            return call_user_func_array([$instance, $method], $arguments);
        }
        throw new \BadMethodCallException();
    }
}
